﻿using LdtJudge.DataEngine.CallInterface.CommHelp;
using LdtJudge.DataEngine.CallInterface.ConfiCenter;
using LdtJudge.DataEngine.Model.DataModel.CommonManageModel;
using LdtJudge.DataEngine.Model.DataModel.SystemtableModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace LdtJudge.DataEngine.Data
{
    /// <summary>
    /// 数据库通用处理类
    /// 该类的定位主要是操作关于系统库中通用的操作逻辑
    /// </summary>
    public class SqlSysCommon : SqlComDataHandle
    {
        private static string connsys = "Ldt_JudgeDocSystem";
        //private static SqlHandle sqhandle;

        private static IFreeSql freeSql = FreeSQLHelp.GetFreeSQL("Ldt_JudgeDocSystem");
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionstring"></param>

        public SqlSysCommon(string key)
        {
            if (!string.IsNullOrEmpty(key))
            {
                freeSql = FreeSQLHelp.GetFreeSQL(key);
            }
            else
            {
                freeSql = FreeSQLHelp.GetFreeSQL("Ldt_JudgeDocSystem");
            }

        }
        ///// <summary>
        ///// 创建表的主键ID
        ///// </summary>
        ///// <param name="tablename">表名称</param>
        ///// <returns></returns>
        //public static string CreateTableKey(string tablename)
        //{
        //    return sqhandle.CreateKeyID(tablename);
        //}

        /// <summary>
        /// 根据AT_ID获取字典构成类型
        /// </summary>
        /// <param name="AT_ID">字典类型ID</param>
        /// <returns></returns>
        public string GetArgumentType_ConstituteMode(string AT_ID)
        {
            //string sqlstr = $"SELECT AT_WordsMode FROM dbo.SYS_ArgumentType WHERE AT_ID='{AT_ID}'";
            var tabel = freeSql.Select<SYS_ArgumentType>().Where(a => a.AT_ID == AT_ID);
            if (tabel != null)
            {
                return tabel.ToList()[0].AT_WordsMode.ToString();
            }
            return "";
        }

        public List<ArgumentRel> GetArgumentRel(string id) 
        {
            IFreeSql freeSqlnew = FreeSQLHelp.GetFreeSQL("LdtCommonManage"); ;

            var tabel = freeSqlnew.Select<ArgumentRel>().Where(a => a.CM_ID == id);
           
            return tabel?.ToList();
        }

        public string GetEvidPurpose(string CD_Code) 
        {
            string value = "";
            var tabledoctype = freeSql.Select<SYS_CaseType_DocType>().Where(a => a.CD_Code == CD_Code).ToList();
            if (tabledoctype!=null && tabledoctype.Count>0)
            {
                string cdid = tabledoctype[0].CD_ID;
                var evidPurposeTabel = freeSql.Select<EB_EvidPurpose>().Where(a => a.CD_ID == cdid).ToList();
                if (evidPurposeTabel!=null && evidPurposeTabel.Count>0)
                {
                    value = evidPurposeTabel[0].EP_EvidPurpose;
                }
            }
            return value;
        }
        /// <summary>
        /// 根据案件类型获取案件的类型编号
        /// Returns the type of the case.
        /// </summary>
        /// <param name="caseType">Type of the case.</param>
        /// <returns></returns>
        public string GetCaseTypeID(string caseType)
        {
            //string sqlStr = "select CC_ID from SYS_Casetype_CaseCauseRel where CaseTypeID=(select CaseTypeID from SYS_CaseType where CT_Code='" + caseType.ToLower().Trim() + "')";
            string cc_id = freeSql.Select<SYS_Casetype_CaseCauseRel, SYS_CaseType>()
                .InnerJoin((cre, ct) => cre.CaseTypeID == ct.CaseTypeID)
                .Where((cre, ct) => ct.CT_Code == caseType.ToLower().Trim()).ToOne((cre, ct) => cre.CC_ID);
            //DbHelperSQL oBase = DbHelperSQLFactory.CreateDatabase("System");
            //DataTable oTb = oBase.ExecuteDataSet(sqlStr).Tables[0];
            //if (oTb == null)
            //{
            //    return string.Empty;
            //}
            //if (oTb.Rows.Count > 0)
            //{
            //    return oTb.Rows[0][0].ToString();
            //}
            //else
            //{
            //    return string.Empty;
            //}
            return cc_id;
        }
        /// <summary>
        /// 根据AT_ID获取关联的数据内容
        /// </summary>
        /// <param name="AT_ID"></param>
        /// <returns></returns>
        //public DataTable GetArgumentTable(string AT_ID)
        //{
        //    return sqhandle.GetTableData("SYS_Argument", "AT_ID", AT_ID);
        //}

        /// <summary>
        /// 根据字典ID和案件类型获取字典实体
        /// </summary>
        /// <param name="AT_ID">字典编号</param>
        /// <param name="casetype">案件类型</param>
        /// <returns></returns>
        //public List<SYS_Argument> GetArgumentTableValue(string AT_ID, string casetype)
        public List<SYS_Argument> GetArgumentTableValue(string AT_ID)
        {
            //string sqlcode = "SELECT * from SYS_Argument where AT_ID=(SELECT TOP 1 AT_ID from SYS_ArgumentType" +
                //" where AT_Code = (SELECT top 1 AT_Code from SYS_ArgumentType where AT_ID='" + AT_ID + "') and AT_RelatCaseType='" + casetype + "')";
            //List<SYS_Argument> sYS_ArgumentList = freeSql.Select<SYS_Argument, SYS_ArgumentType>().LeftJoin<SYS_ArgumentType>((a, b) => a.AT_ID == b.AT_ID && b.AT_ID == AT_ID).ToList();
            List<SYS_Argument> sYS_ArgumentList = freeSql.Select<SYS_Argument>().Where(a => a.AT_ID == AT_ID).ToList();
            //if (sYS_ArgumentList.Count == 0)
            //{
            //    sYS_ArgumentList = freeSql.Select<SYS_Argument, SYS_ArgumentType>().LeftJoin<SYS_ArgumentType>((a, b) => a.AT_ID == b.AT_ID && b.AT_ID == AT_ID).Where((a, b) => b.AT_RelatCaseType == "common").ToList();
            //}
            return sYS_ArgumentList;
        }

        public string GetArgumentATidValue(string AT_Code,string CCid) 
        {
            string atid = "";
            List<SYS_ArgumentType> sYS_ArgumentTypes = freeSql.Select<SYS_ArgumentType>().Where(a => a.AT_Code == AT_Code && a.AT_CauseID == CCid).ToList();
            if (sYS_ArgumentTypes.Count>0)
            {
                atid = sYS_ArgumentTypes[0].AT_ID;
            }
            return atid;
        }

        /// <summary>
        /// 执行SQL语句返回DataTabel
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            sql = sql.Replace("[", "").Replace("]", "");
            //return sqhandle.GetSqlHelper.ExecuteReader(sql);
            return freeSql.Ado.ExecuteDataTable(sql);
        }

        public List<SYS_Court> GetDataTableCourt(string sql)
        {
            string sqls = freeSql.Select<SYS_Court>().Where(sql).ToSql();
            List<SYS_Court> sYS_CaseTypeList = freeSql.Select<SYS_Court>().Where(sql).ToList();
            return sYS_CaseTypeList;
        }

        public List<SYS_ProsecutorDept> GetDataTableSys_prosecutordept(string sql)
        {
            List<SYS_ProsecutorDept> sYS_CaseTypeList = freeSql.Select<SYS_ProsecutorDept>().Where(sql).ToList();
            return sYS_CaseTypeList;
        }

        public string GetMarketSupervision(string sqlvalue) 
        {
            List<SYS_Organ_MarketSupervision> sYS_Organ_MarketList = freeSql.Select<SYS_Organ_MarketSupervision>().Where(a => a.MS_Name == sqlvalue).ToList();
            if (sYS_Organ_MarketList!=null && sYS_Organ_MarketList.Count>0)
            {
                return sYS_Organ_MarketList[0].MS_ID;
            }
            return "";
        }

        public string GetMarketSupervisionCaseid(string sqlvalue)
        {
            List<SYS_CaseCause> sYS_Organ_MarketList = freeSql.Select<SYS_CaseCause>().Where(a => a.CC_Name == sqlvalue ).ToList();
            if (sYS_Organ_MarketList != null && sYS_Organ_MarketList.Count > 0)
            {
                return sYS_Organ_MarketList[0].CC_ID;
            }
            return "";
        }

        /// <summary>
        /// 获取案件类型
        /// </summary>
        /// <returns></returns>
        public List<SYS_CaseType> GetCaseType()
        {
            //string strSql = " select CaseTypeID as ID,CT_Name as Name,CT_Code as Code,CT_ParentID as ParentID from SYS_CaseType ";
            List<SYS_CaseType> sYS_CaseTypeList = freeSql.Select<SYS_CaseType>().ToList();
            return sYS_CaseTypeList;
        }

        /// <summary>
        /// 获取案件类型信息
        /// </summary>
        /// <param name="CTName">案号类型名称</param>
        /// <returns></returns>
        public string GetCaseTypeByCT_Name(string CTName)
        {
            //string strSql = " select * from SYS_CaseType where ct_name='" + CTName + "'";
            List<SYS_CaseType> sYS_CaseTypeList = freeSql.Select<SYS_CaseType>().Where(a => a.CT_Name == CTName).ToList();
            if (sYS_CaseTypeList.Count > 0)
            {
                return sYS_CaseTypeList[0].CT_Code;
            }

            return "";
        }

        /// <summary>
        /// 根据案件类型获取文书类型
        /// </summary>
        /// <param name="caseTypeCode"></param>
        /// <returns></returns>
        public List<SYS_CaseType_DocType> GetDocTypeDataByCaseTypeID(string caseTypeCode)
        {
            List<SYS_CaseType_DocType> sYS_CaseType_List = freeSql.Select<SYS_CaseType_DocType, SYS_CaseTypeDocRel>().InnerJoin((a, b) => a.CD_Code == b.CD_Code).Where((a, b) => b.CDR_IsAnalysableDoc == 1 && b.CT_Code == caseTypeCode && a.CD_DocType == 1)?.ToList();
            string sql = freeSql.Select<SYS_CaseType_DocType, SYS_CaseTypeDocRel>().InnerJoin((a, b) => a.CD_Code == b.CD_Code).Where((a, b) => b.CDR_IsAnalysableDoc == 1 && b.CT_Code == caseTypeCode && a.CD_DocType == 1).ToSql();
            return sYS_CaseType_List;
        }

        /// <summary>
        /// 根据字典标识和原文值得到字典值
        /// </summary>
        /// <param name="at_code"></param>
        /// <param name="am_name"></param>
        /// <returns></returns>
        public List<SYS_Argument> GetDictionaryId(string at_code, string am_name)
        {
            List<SYS_Argument> sYS_ArgumentTypeList = freeSql.Select<SYS_Argument, SYS_ArgumentType>().InnerJoin((a, b) => a.AT_ID == b.AT_ID).Where((a, b) => a.AM_Name == am_name && b.AT_Code == at_code).ToList();
            string balue = freeSql.Select<SYS_Argument, SYS_ArgumentType>().InnerJoin((a, b) => a.AT_ID == b.AT_ID).Where((a, b) => a.AM_Name == am_name && b.AT_Code == at_code).ToSql();
            return sYS_ArgumentTypeList;
        }

        /// <summary>
        /// 所有标识
        /// </summary>
        /// <param name="at_code"></param>
        /// <returns></returns>
        public List<SYS_ArgumentType> GetDictionaryIdList(string at_code)
        {
            //string sqlstr = "select b.AM_Code,b.AM_Name from SYS_ArgumentType as a inner join SYS_Argument as b on a.AT_ID = b.AT_ID where a.AT_Code = '" + at_code + "' ";
            List<SYS_ArgumentType> sYS_ArgumentTypeList = freeSql.Select<SYS_ArgumentType, SYS_Argument>().InnerJoin<SYS_Argument>((a, b) => a.AT_ID == b.AT_ID).Where((a, b) => a.AT_Code == at_code).ToList();
            return sYS_ArgumentTypeList;
        }

        /// <summary>
        /// 正则内容
        /// </summary>
        /// <param name="at_code"></param>
        /// <returns></returns>
        public string GetDictionaryWordReg(string at_code)
        {
            //string sql = @"select AT_MatchKeyWord from SYS_ArgumentType where AT_Code ='" + at_code + "'";
            List<SYS_ArgumentType> sYS_ArgumentTypeList = freeSql.Select<SYS_ArgumentType>().Where(a => a.AT_Code == at_code)?.ToList();
            if (sYS_ArgumentTypeList.Count > 0)
            {
                return sYS_ArgumentTypeList[0].AT_MatchKeyWord;
            }
            return "";
        }


        /// <summary>
        /// 根据ID获取字典值
        /// </summary>
        /// <param name="at_id">字典ID</param>
        /// <returns></returns>
        public string GetDictionaryName(string at_id)
        {
            //string sql = "SELECT * FROM SYS_Argument WHERE AM_Code='" + at_id + "'";
            //DataTable dataTable= sqhandle.GetSqlHelper.ExecuteReader(sql);

            var table = freeSql.Select<SYS_Argument>().Where(a => a.AM_Code == at_id);

            string value = "";
            //if (dataTable.Rows.Count>0)
            //{
            //    value = dataTable.Rows[0]["am_name"].ToString();
            //}
            if (table != null)
            {
                value = table.ToList()[0].AM_Name;
            }
            return value;

        }
        /// <summary>
        /// 提取配置信息
        /// </summary>
        /// <param name="cd_code">cd_code</param>
        /// <returns></returns>
        public List<SYS_CaseType_DocType> Getsys_CaseType_DocType(string cd_code)
        {
            //string strsql = @"select * from sys_casetype_doctype where cd_code='" + cd_code + "'";
            List<SYS_CaseType_DocType> sYS_CaseType_DocTypeList = freeSql.Select<SYS_CaseType_DocType>().Where(a => a.CD_Code == cd_code)?.ToList();
            return sYS_CaseType_DocTypeList;
        }
        /// <summary>
        /// 获取表信息
        /// </summary>
        /// <param name="cd_id">cd_id</param>
        /// <returns></returns>
        public List<SYS_CaseCauseElement_EvidRel> GetSYS_CaseCauseElement_EvidRel(string cd_id,string casecausecode)
        {
            //string strsql = @"select * from SYS_CaseCauseElement_EvidRel where cd_id='" + cd_id + "'";
            List<SYS_CaseCauseElement_EvidRel> sYS_CaseCauseElement_EvidRelList = freeSql.Select<SYS_CaseCauseElement_EvidRel>().Where(a => a.CD_ID == cd_id && a.Causeidrel==casecausecode)?.ToList();
            return sYS_CaseCauseElement_EvidRelList;
        }

        /// <summary>
        /// 获取对应XMLPATH对应的CC_ID
        /// </summary>
        /// <param name="xmlpath">xmlpath路径</param>
        /// <returns></returns>
        public string GetSysCaseCuaseCCID(string xmlpath)
        {
            string value = "";
            //string strsql = "select * from SYS_CaseCause where xmlpath='" + xmlpath + "'";
            //DataTable dataTable = sqhandle.GetSqlHelper.ExecuteReader(strsql);
            //if (dataTable.Rows.Count > 0)
            //{
            //    value = dataTable.Rows[0]["cc_id"].ToString();
            //}
            var list = freeSql.Select<SYS_CaseCause>().Where(a => a.XmlPath == xmlpath)?.ToList();
            if (list.Count > 0)
            {
                value = list[0].CC_ID;
            }
            return value;
        }

        /// <summary>
        /// 根据层级获取地域信息实体
        /// </summary>
        /// <param name="level">层级</param>
        /// <returns></returns>
        public List<SYS_District> GetSYS_DistrictListByLevel(int level)
        {
            List<SYS_District> sYS_DistrictList = freeSql.Select<SYS_District>().Where(a => a.DT_Level == level).ToList();
            return sYS_DistrictList;
        }

        /// <summary>
        /// 根据层级与上级ID查询地域实体
        /// </summary>
        /// <param name="level">层级</param>
        /// <param name="parentID">上级ID</param>
        /// <returns></returns>
        public List<SYS_District> GetSYS_DistrictListByLevelByParent(int level,string parentID)
        {
            List<SYS_District> sYS_DistrictList = freeSql.Select<SYS_District>().Where(a => a.DT_Level == level && a.DT_ParentID==parentID).ToList();
            return sYS_DistrictList;
        }
        /// <summary>
        /// 根据层级与上级ID
        /// </summary>
        /// <param name="parentID">上级ID</param>
        /// <returns></returns>
        public List<SYS_District> GetSYS_DistrictListByParent(string parentID)
        {
            List<SYS_District> sYS_DistrictList = freeSql.Select<SYS_District>().Where(a => a.DT_ParentID == parentID).ToList();
            return sYS_DistrictList;
        }

        public List<SYS_District> GetSYS_DistrictListByID(string Code)
        {
            List<SYS_District> sYS_DistrictList = freeSql.Select<SYS_District>().Where(a => a.DT_Code == Code).ToList();
            return sYS_DistrictList;
        }

        /// <summary>
        /// 获取法院信息
        /// </summary>
        /// <param name="ID">法院ID</param>
        /// <returns></returns>
        public List<SYS_Court> GetSYS_CourtListByID(string ID) 
        {
            List<SYS_Court> sYS_CourtList = freeSql.Select<SYS_Court>().Where(a => a.CourtID == ID).ToList();
            return sYS_CourtList;
        }

        /// <summary>
        /// 根据银行名称获取银行ID 
        /// </summary>
        /// <param name="bankName">银行名称</param>
        /// <returns></returns>
        public string GetSYS_BankIdByName(string bankName)
        {
            string bankId = freeSql.Select<SYS_Organ_bank>()
                                   .Where(x=>x.OB_Name==bankName)
                                   .ToOne(x => x.OB_ID);
            return bankId;
        }

        /// <summary>
        /// 根据案由名称获取案由信息
        /// </summary>
        public SYS_CaseCause GetCauseByName(string ccName,string casetype)
        {
            var queryList = freeSql.Select<SYS_CaseCause>()
                               .Where(x => x.CC_Name == ccName && (x.CC_NodeType == 12 || x.CC_NodeType == 2) && x.AllParentId.IndexOf(casetype) > -1)
                               .ToList();
            //存在相同名称的案由 取子级案由 比如：走私、贩卖、运输、制造毒品罪1289、1290
            SYS_CaseCause query = new SYS_CaseCause();
            if (queryList.Count == 1)
            {
                query = queryList[0];
            }
            else
            {
                queryList.ForEach(x =>
                {
                    string str = x.AllParentId + ",";
                    if (queryList.Any(x => x.AllParentId.Contains(str)))
                        query = queryList.FirstOrDefault(x => x.AllParentId.Contains(str));
                });
            }
            //string sql = freeSql.Select<SYS_CaseCause>()
            //                   .Where(x => x.CC_Name == ccName && (x.CC_NodeType == 12 || x.CC_NodeType == 2))
            //                   .ToSql();
            return query;
        }


        /// <summary>
        /// 根据案由Id获取案由信息
        /// </summary>
        public SYS_CaseCause GetCauseById(string ccId)
        {
            var query = freeSql.Select<SYS_CaseCause>()
                             .Where(x => x.CC_ID == ccId)
                             .ToOne();
            return query;
        }

        /// <summary>
        /// 通过公共机构名称获取公共机构ID
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public string GetOrganizationIdByName(string name)
        {
            string id = freeSql.Select<SYS_Organization>()
                             .Where(x => x.OZ_Name == name)
                             .ToOne(x => x.OZ_ID);
            return id;
        }

    }
}
